﻿using System;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using SolidQ.CommonLibraries.GestionErrores;


namespace SolidQSqlConnection
{
    /// <summary>
    /// Drivers soportados
    /// </summary>
    public enum DriverConexion
    {
        SqlServer = 0,        
        AS400,
        Access,
        DB2,
        Oracle,
        SqlServerOleDb,
        SqlServerOleDbNativeClient,
        SqlAzure
    }


    public class SolidQSqlConnection
    {
        DriverConexion _driver;
        public DriverConexion Driver
        {
            get { return (_driver); }
            set { _driver = value; }
        }

        private string _applicationName;
        public string ApplicationName
        {
            get { return (_applicationName); }
            set { _applicationName = value; }
        }

        private DbConnection _sqlConnection;
        public DbConnection Connection
        {
            get { return (_sqlConnection); }
        }

        private string _sqlInstanceWithPort;
        public string Instance
        {
            get { return _sqlInstanceWithPort; }
            set { _sqlInstanceWithPort = value; }
        }

        private int _timeout;
        public int ConnectionTimeout
        {
            get { return (_timeout); }
            set { _timeout = value; }
        }

        private bool _useIntegratedSecurity;
        public bool UseIntegratedSecurity
        {
            get { return _useIntegratedSecurity; }
            set { _useIntegratedSecurity = value; }
        }

        private string _sqlUser;
        public string User
        {
            get { return _sqlUser; }
            set { _sqlUser = value; }
        }

        private string _sqlPassword;
        public string Password
        {
            get { return _sqlPassword; }
            set { _sqlPassword = value; }
        }

        private string _database;
        public string Database
        {
            get { return _database; }
            set { _database = value; }
        }

        /// <summary>
        /// Empty constructor. 
        /// DEVELOPER MUST FILL EACH PROPERTY 
        /// </summary>
        public SolidQSqlConnection()
        { }

        public SolidQSqlConnection(string sqlInstanceWithTcpPort, string sqlUser, string sqlPassword, string database)
        {
            this.Init(sqlInstanceWithTcpPort, false, sqlUser, sqlPassword, database);
        }
        public SolidQSqlConnection(DriverConexion driver, string sqlInstanceWithTcpPort, string sqlUser, string sqlPassword, string database)
        {
            this.Init(driver,sqlInstanceWithTcpPort, false, sqlUser, sqlPassword, database);
        }

        public SolidQSqlConnection(string sqlInstanceWithTcpPort, string sqlUser, string sqlPassword)
        {
            this.Init(sqlInstanceWithTcpPort, false, sqlUser, sqlPassword, "tempdb");
        }
        public SolidQSqlConnection(DriverConexion driver, string sqlInstanceWithTcpPort, string sqlUser, string sqlPassword)
        {
            this.Init(driver,sqlInstanceWithTcpPort, false, sqlUser, sqlPassword, "tempdb");
        }

        public SolidQSqlConnection(string sqlInstanceWithTcpPort, bool useIntegratedSecurity)
        {
            this.Init(sqlInstanceWithTcpPort, useIntegratedSecurity, "", "", "tempdb");
        }
        public SolidQSqlConnection(DriverConexion driver, string sqlInstanceWithTcpPort, bool useIntegratedSecurity)
        {
            this.Init(driver, sqlInstanceWithTcpPort, useIntegratedSecurity, "", "", "tempdb");
        }

        public SolidQSqlConnection(string sqlInstanceWithTcpPort, bool useIntegratedSecurity, string database)
        {
            this.Init(sqlInstanceWithTcpPort, useIntegratedSecurity, "", "", database);
        }
        public SolidQSqlConnection(DriverConexion driver, string sqlInstanceWithTcpPort, bool useIntegratedSecurity, string database)
        {
            this.Init(sqlInstanceWithTcpPort, useIntegratedSecurity, "", "", database);
        }

        public SolidQSqlConnection(string sqlInstanceWithTcpPort, bool useIntegratedSecurity, string sqlUser, string sqlPassword, string database)
        {
            this.Init(sqlInstanceWithTcpPort, useIntegratedSecurity, sqlUser, sqlPassword, database);
        }
        public SolidQSqlConnection(DriverConexion driver, string sqlInstanceWithTcpPort, bool useIntegratedSecurity, string sqlUser, string sqlPassword, string database)
        {
            this.Init(driver, sqlInstanceWithTcpPort, useIntegratedSecurity, sqlUser, sqlPassword, database);
        }


        private void Init(string sqlInstanceWithTcpPort, bool useIntegratedSecurity, string sqlUser, string sqlPassword, string database)
        {
            // by default we use sql server
            this.Init(DriverConexion.SqlServer, sqlInstanceWithTcpPort, useIntegratedSecurity, sqlUser, sqlPassword, database);
        }

        private void Init(DriverConexion driver, string sqlInstanceWithTcpPort, bool useIntegratedSecurity, string sqlUser, string sqlPassword, string database)
        {
            _sqlInstanceWithPort = sqlInstanceWithTcpPort;
            _useIntegratedSecurity = useIntegratedSecurity;
            _sqlUser = sqlUser;
            _sqlPassword = sqlPassword;
            _database = database;
            _driver = driver;
            _applicationName = "SQM " + this.GetType().Name;

            this.InitSqlConnection();
        }

        private void InitSqlConnection()
        {
                        try
            {
                switch (_driver)
                {
                    case DriverConexion.SqlServer | DriverConexion.SqlAzure:
                        _sqlConnection = new SqlConnection(ConnectionString);
                        break;
                    case DriverConexion.SqlServerOleDb:
                        _sqlConnection = new OleDbConnection(ConnectionString);
                        break;
                    case DriverConexion.SqlServerOleDbNativeClient:
                        _sqlConnection = new OleDbConnection(ConnectionString);
                        break;
                    default: throw new SolidQException("Error in creating SqlConnection.", @"Connection string:
" + ConnectionString + @"

Message:
" , null);
                };
            }
            catch (Exception e)
            {
                throw new SolidQException("Error in creating SqlConnection.", @"Connection string:
" + ConnectionString + @"

Message:
" + e.Message, e.InnerException);
            }
        }

        /// <summary>
        /// Builds the SQL Server connection using or not, integratetd security depending on user settings
        /// </summary>
        /// <param name="useIntegratedSecurity"></param>
        /// <returns></returns>
        public string ConnectionString
        {
            get 
            {
                return (this.GetConnectionString(_driver));
            }
        }
        
        /// <summary>
        /// Builds the SQL Server connection string depending of the driver we want
        /// </summary>
        /// <param name="driver"></param>
        /// <returns></returns>
        public string GetConnectionString(DriverConexion driver)
        {
            return (BuildConnectionString(driver, _sqlInstanceWithPort, _database,_useIntegratedSecurity, _sqlUser, _sqlPassword, _applicationName));                
        }


        /// <summary>
        /// ECB: Static method to quick connection string building
        /// </summary>
        /// <param name="instance_with_port"></param>
        /// <param name="database"></param>
        /// <param name="use_integrated_security"></param>
        /// <param name="user"></param>
        /// <param name="password"></param>
        /// <param name="application_name"></param>
        /// <returns></returns>
        public static string BuildConnectionString(string instance_with_port,string database, bool use_integrated_security, string user, string password,string application_name)
        {
            return (BuildConnectionString(DriverConexion.SqlServer, instance_with_port, database, use_integrated_security, user, password, application_name));          
        }

        public static string BuildConnectionString(DriverConexion driver, string instance_with_port, string database, bool use_integrated_security, string user, string password, string application_name)
        {
            string connectionString;            
            switch (driver)
            {
                case DriverConexion.SqlServer:
                    if (use_integrated_security)
                        connectionString = "Server=" + instance_with_port + ";Database = " + database + ";Integrated Security=SSPI;Application Name=\"" + application_name + "\"";
                    else
                        connectionString = "Server = " + instance_with_port + "; Database = " + database + "; User ID = " + user + "; Password = " + password + ";Application Name=\"" + application_name + "\";Persist Security Info=True";
                    break;
                case DriverConexion.SqlAzure:
                    if (use_integrated_security)
                        throw new ArgumentException("SQL Azure does not support Integrated Security");
                    else
                    {
                        string _serverName = instance_with_port.Split('.')[0];
                        connectionString = "Server= " + instance_with_port + "; Database = " + database + "; User ID = " + user + "@" + _serverName + "; Password = " + password + ";Encrypt=True;";
                    }
                    break;
                case DriverConexion.SqlServerOleDb:
                    if (use_integrated_security)
                        connectionString = "Data Source=" + instance_with_port + ";Initial Catalog = " + database + ";Integrated Security=SSPI;Application Name=\"" + application_name + "\"";
                    else
                        connectionString = "Data Source = " + instance_with_port + ";Initial Catalog= " + database + "; User ID = " + user + "; Password = " + password + ";Application Name=\"" + application_name + "\";Persist Security Info=True";

                    /// Si la cadena de conexión es para OleDb, le tengo que especificar el proveedor de native client 10                    
                    connectionString += ";Provider=SQLNCLI10.1";

                    break;
                case DriverConexion.AS400:
                    if (use_integrated_security)
                        throw new NotImplementedException("Connection string not implemented for the driver: " + driver);
                    else
                        connectionString = "Data Source=" + instance_with_port + ";User ID=" + user + ";Initial Catalog=" + database + ";Provider=DB2OLEDB;Persist Security Info=True;Defer Prepare=False;Derive Parameters=False;Rowset Cache Size=0;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=" + instance_with_port + ";Network Port=446;Package Collection=QGPL;Default Schema=" + database + ";DBMS Platform=DB2/AS400;Process Binary as Character=True;Connection Pooling=False;Units of Work=RUW;";
                    break;
                case DriverConexion.Access:
                    if (use_integrated_security)
                        throw new NotImplementedException("Connection string not implemented for the driver: " + driver);
                    else
                    {
                        //CON JET 4.0
                        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + instance_with_port + ";";
                        if (!String.IsNullOrEmpty(user))
                        {
                            connectionString += "User Id=" + user + ";";
                        }
                        if (!String.IsNullOrEmpty(password))
                        {
                            connectionString += "Jet OLEDB:Database Password=" + password + ";";
                        }

                        //CON ACE.OLEDB.12.0
                        /*
                        connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + sqlInstance + ";";
                        if (!String.IsNullOrEmpty(sqlUser))
                        {
                            connectionString+="User ID="+sqlUser+";";
                        }
                        if (!String.IsNullOrEmpty(sqlPassword))
                        {
                            connectionString += "Database Password=" + sqlPassword + ";";
                        }
                        */
                    }
                    break;

                case DriverConexion.DB2:
                    if (use_integrated_security)
                        throw new NotImplementedException("Connection string not implemented for the driver: " + driver);
                    else
                        connectionString = "Data Source=" + database + ";User ID=" + user + ";Password = " + password + ";Provider=IBMDADB2.DB2COPY1;Persist Security Info=True;Location=" + instance_with_port + ";Extended Properties=;";
                    break;
                case DriverConexion.Oracle:
                    if (use_integrated_security)
                        throw new NotImplementedException("Connection string not implemented for the driver: " + driver);
                    else
                        connectionString = "Data Source=" + database + ";User Id=" + user + ";Password = " + password + ";Provider=OraOLEDB.Oracle;";

                    break;

                default: throw new NotImplementedException("Connection string not implemented for the driver: " + driver);
            }

            return (connectionString);
        }

        public void Open()
        {
            if (_sqlConnection == null)
            {
                this.InitSqlConnection();
            }
            if (_sqlConnection.State == System.Data.ConnectionState.Closed)
                _sqlConnection.Open();
        }

        public void Close()
        {
            if (_sqlConnection.State != System.Data.ConnectionState.Closed)
                _sqlConnection.Close();
        }

        /// <summary>
        /// If connection is open, then closes and reopen the connection
        /// </summary>
        public void ResetConnection()
        {
            if (_sqlConnection !=null && _sqlConnection.State == System.Data.ConnectionState.Open)
            {
                _sqlConnection.Close();
                _sqlConnection.Open();
            }
        }

        public bool TestConnection()
        {
            bool retorno = false;
            try
            {
                this.Open();
                retorno = true;                
            }            
            catch (Exception e)
            {                
                throw new SolidQException("SQL connection problem: "+ this.ConnectionString, e.Message, e.InnerException);
            }
            finally
            {
                this.Close();
            }
            return (retorno);
        }

        public bool IsConnected()
        {
            return (_sqlConnection.State == System.Data.ConnectionState.Open);
        }

  
     
    }
}

